import akshare as ak
import pandas as pd
from datetime import datetime, timedelta


# 1. 获取过去一年日期范围
# 数据从13年10月18日开始有铁矿石
# end_date = datetime.now().strftime("%Y%m%d")
# start_date = (datetime.now() - timedelta(days=365)).strftime("%Y%m%d")


# 2. 获取焦煤主连数据
def get_main_contract_data(symbol):
    # 默认从开头取到现在
    df = ak.futures_main_sina(symbol=symbol)
    df['date'] = pd.to_datetime(df['日期']).dt.strftime('%Y-%m-%d')
    # df = df[(df['date'] >= start) & (df['date'] <= end)]
    return df[['date', '开盘价', '收盘价']].rename(columns={'开盘价': 'open', '收盘价': 'close'})


# 获取焦煤(JM0)和焦炭(J0)数据
rb_data = get_main_contract_data("RB0")
jm_data = get_main_contract_data("JM0")
j_data = get_main_contract_data("J0")
i_data = get_main_contract_data("I0")

# 3. 合并数据并计算比值
# 合并焦炭焦煤
merged_data = pd.merge(jm_data, j_data, on='date', suffixes=('_JM', '_J'))
# 合并螺纹钢
merged_data = pd.merge(merged_data, rb_data, on='date', suffixes=('', '_RB'))
merged_data.rename(columns={'open': 'open_RB', 'close': 'close_RB'}, inplace=True)
# 合并铁矿石
merged_data = pd.merge(merged_data, i_data, on='date', suffixes=('', '_I'))
merged_data.rename(columns={'open': 'open_I', 'close': 'close_I'}, inplace=True)

# 计算焦炭、焦煤套利
merged_data['J/JM_Open_Ratio'] = merged_data['open_J'] / merged_data['open_JM']
merged_data['J/JM_Close_Ratio'] = merged_data['close_J'] / merged_data['close_JM']
merged_data['J/JM_Ratio_Diff'] = merged_data['J/JM_Close_Ratio'] - merged_data['J/JM_Open_Ratio']

# 计算螺纹钢、焦炭套利
merged_data['RB/J_Open_Ratio'] = merged_data['open_RB'] / merged_data['open_J']
merged_data['RB/J_Close_Ratio'] = merged_data['close_RB'] / merged_data['close_J']
merged_data['RB/J_Ratio_Diff'] = merged_data['RB/J_Close_Ratio'] - merged_data['RB/J_Open_Ratio']

# 计算螺纹钢、焦煤
merged_data['RB/JM_Open_Ratio'] = merged_data['open_RB'] / merged_data['open_JM']
merged_data['RB/JM_Close_Ratio'] = merged_data['close_RB'] / merged_data['close_JM']
merged_data['RB/JM_Ratio_Diff'] = merged_data['RB/JM_Close_Ratio'] - merged_data['RB/JM_Open_Ratio']

# 4. 导出Excel（优化列名与格式）
output_columns = {
    'date': '日期',
    'open_RB': '螺纹开盘',
    'close_RB': '螺纹收盘',
    'open_I': '铁矿开盘',
    'close_I': '铁矿收盘',
    'open_J': '焦炭开盘',
    'close_J': '焦炭收盘',
    'open_JM': '焦煤开盘',
    'close_JM': '焦煤收盘',
    'RB/J_Open_Ratio': 'RB/J开盘比值',
    'RB/J_Close_Ratio': 'RB/J收盘比值',
    'RB/J_Ratio_Diff': 'RB/J收开差',
    'RB/JM_Open_Ratio': 'RB/JM开盘比值',
    'RB/JM_Close_Ratio': 'RB/JM收盘比值',
    'RB/JM_Ratio_Diff': 'RB/JM收开差',
    'J/JM_Open_Ratio': 'J/JM开盘比值',
    'J/JM_Close_Ratio': 'J/JM收盘比值',
    'J/JM_Ratio_Diff': 'J/JM收开差'
}
merged_data.rename(columns=output_columns, inplace=True)
merged_data.to_excel("焦炭焦煤比值分析.xlsx", index=False, engine='openpyxl')
